package guoanmaker.operator.business.dao;


import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import guoanmaker.operator.business.modelnobind.ActivityInfor;
import guoanmaker.operator.business.modelnobind.CommodityMove;
import guoanmaker.operator.business.modelnobind.SecckillAndCommodMove;
import guoanmaker.operator.business.modelnobind.SecckillMove;
import guoanmaker.operator.business.modelnobind.SkillAndCommdMove;
@Repository
public class SecckillCommodityDao {
	@PersistenceContext
	private EntityManager manager;
	
	public CommodityMove selectByCommodityCode(Integer commodCode)throws Exception{
		String sql = "SELECT com.id, com.commodity_name,com.final_price,com.commodity_code,com.stock ,com.commodity_smallimage,com.commodity_state FROM t_provide_commodity com WHERE com.commodity_code =:commodCode";
		Query query = manager.createNativeQuery(sql, CommodityMove.class);
		query.setParameter("commodCode", commodCode);
		CommodityMove enlist =  (CommodityMove) query.getSingleResult();
		return enlist;
	}
	
	@SuppressWarnings("unchecked")
	public List<SecckillAndCommodMove> selectSecckillAndCommod(String activityId, Date skTime)throws Exception {
		String sql = "SELECT tos.id, tos.commod_id, tos.commod_final_price, tos.sk_price, tos.sk_num, tos.activity_id, tpc.commodity_name, tpc.commodity_smallimage, tpb.business_name,tpc.type FROM t_operator_seckill tos, t_provide_commodity tpc, t_provide_business tpb WHERE tos.activity_id =:activityId AND tos.sk_start_time <=:skTime AND tos.sk_end_time>=:skTime AND tos.commod_id = tpc.id AND tpc.business_id = tpb.id ORDER BY tos.weight asc";
		Query query = manager.createNativeQuery(sql, SecckillAndCommodMove.class);
		query.setParameter("activityId", activityId);
		query.setParameter("skTime", skTime);
		List<SecckillAndCommodMove> enlist = query.getResultList();
		return enlist;
	}
	
	@SuppressWarnings("unchecked")
	public List<SecckillMove> selectSecckillByActivityId(String activityId){
		String sql = "SELECT osk.id, osk.sk_start_time,osk.sk_end_time FROM t_operator_seckill osk WHERE osk.activity_id=:activityId GROUP BY(osk.sk_start_time)";
		Query query = manager.createNativeQuery(sql, SecckillMove.class);
		query.setParameter("activityId", activityId);
		List<SecckillMove> enlist = query.getResultList();
		return enlist;	
	}
	
	public ActivityInfor selectActivityInforByActivityId(String activityId){
		String sql = "SELECT oed.id,  oed.event_pic_url ,oed.event_pic_app_url FROM t_operator_event_details oed where oed.id=:activityId";
		Query query = manager.createNativeQuery(sql, ActivityInfor.class);
		query.setParameter("activityId", activityId);
		ActivityInfor enlist = (ActivityInfor) query.getSingleResult();
		return enlist;	
	}
	
	@SuppressWarnings("unchecked") 
	public List<SkillAndCommdMove> selectSkillAndCommdMoveByActivityId(String activityId){
		String sql = "SELECT tos.id,tos.commod_id,tos.commod_code,tos.commod_final_price,tos.sk_price,tos.sk_num,tos.sk_start_time, tos.sk_end_time, tos.activity_id, tpc.commodity_name, tpc.commodity_smallimage, tpc.stock, tpb.business_name FROM t_operator_seckill tos, t_provide_commodity tpc, t_provide_business tpb WHERE tos.activity_id =:activityId AND tos.commod_id = tpc.id  AND tpc.business_id = tpb.id ORDER BY tos.sk_start_time ASC, tos.weight asc";
		Query query = manager.createNativeQuery(sql, SkillAndCommdMove.class);
		query.setParameter("activityId", activityId);
		List<SkillAndCommdMove> enlist = query.getResultList();
		return enlist;	
	}
	
}
